import xlrd
import numpy as np
import pandas as pd


from Utils import IOUtil
from Utils.TimeUtil import *
from Service.MyLogger import MyLogger
import os,logging
logFileName =  os.path.basename(__file__).split(".")[0]
from Statistics.COMMON import commonLogger
log = commonLogger # MyLogger(r"./Logs/%s.log" % (logFileName), logging.DEBUG, logging.DEBUG)
def xlsx2List(fileName,isFromFisrtRow=False):
    workBook = xlrd.open_workbook(filename=fileName)
    workSheet = workBook.sheet_by_index(0)
    # 根据行获取所有单元格数据
    outArr = []
    for i in range(0 if isFromFisrtRow else 1,workSheet.nrows):
        innerArr = []
        for ee in workSheet.row(i):
            innerArr.append(ee.value)
        outArr.append(innerArr)
    IOUtil.printArr(outArr)
    return outArr
def resTable2xlsx(resTable,fileName='aa.xlsx',sheetName='sheet1',columns=None,widths=None,rows=None,isIndex=True):
    if len(resTable)==0:
        pass #2204# print("列数为0.............")
        return
    # a = np.array(resTable) # 以文本格式存储数值
    a = resTable # 不以文本格式存储数值
    # transform a to pandas DataFrame
    a_pd = pd.DataFrame(a)
    if columns != None:
        a_pd.columns = columns
    # if rows != None:
        # a_pd.index = rows
    a_pd.index = a_pd.index + 1
    a_pd.index.name = '序号'
    # create writer to write an excel file
    writer = pd.ExcelWriter(fileName)
    # write in ro file, 'sheet1' is the page title, float_format is the accuracy of data
    a_pd.to_excel(writer, sheetName, float_format='%.6f',index=isIndex)
    workbook = writer.book
    worksheet = writer.sheets[sheetName]
    # for i, col in enumerate(a_pd.columns):
    #     # find length of column i
    #     column_len = a_pd[col].astype(str).str.len().max()
    #     # Setting the length if the column header is larger
    #     # than the max column value length
    #     column_len = max(column_len, len(col)) + 2
    #     # set the column length
    #     print("04081738_i:",i,"col:",col,"a_pd[col]:",a_pd[col],"column_len:",column_len)
    #     worksheet.set_column(i, i, column_len)
    # worksheet.set_column(0,len(a[0]),14)

    # 设置各列宽度
    # worksheet.set_column(0, 1, 7)
    # worksheet.set_column(1, 2, 12)
    # worksheet.set_column(2, len(a[0]), 12)
    if widths:
        for i in range(len(widths)):
            worksheet.set_column(i+1,i+1,widths[i])

    # if rows:
    #     a_pd.index.name = '序号'
    # save file
    writer.save()
    # close writer
    # writer.close() # 会提示 UserWarning: Calling close() on already closed file.
def dictArr2xlsx(dictArr,fileName='aa.xlsx',sheetName='sheet1',rows=None,specialDealFor=""):
    if dictArr==None or len(dictArr)<=0:
        return
    columns = list(dictArr[0].keys())
    resTable = [list(ee.values()) for ee in dictArr]
    if specialDealFor == "general300s":
        for i in range(len(resTable)):
            resTable[i][0] = stampS2TimeStr(resTable[i][0])
    elif specialDealFor == "slowSql":
        tmpDict = {"sqlLabel":"sql标识","count":"出现总次数","Query_time_mean":"平均查询时间","Query_time_sum":"总查询时间","Query_time_std":"查询时间标准差","Query_time_max":"最大查询时间","Query_time_min":"最小查询时间",
         "Rows_examined_mean":"平均查询行数","Rows_examined_std":"查询行数标准差","demo":"一个例子"}
        columns = [tmpDict[ee] for ee in columns]
    resTable2xlsx(resTable=resTable,fileName=fileName,sheetName=sheetName,columns=columns,rows=rows)

def toManySheetsByColumns(filePath:str, contentDict:dict, widths=None):
    with pd.ExcelWriter(filePath) as writer:
        for k,v in contentDict.items():
            df = pd.DataFrame({None: v})
            df.to_excel(writer, sheet_name=k, index=False)
            worksheet = writer.sheets[k]
            if widths:
                for i in range(len(widths)):
                    # 没有 Index 时,不用+1
                    worksheet.set_column(i + 0, i + 0, widths[i])
    pass #2204# print("write over...")

def toOneSheetByRows(filePath:str, contentDict:dict, columns=None, widths=None, isIndex=True):
    try:
        with pd.ExcelWriter(filePath) as writer:
            for k,v in contentDict.items():
                df = pd.DataFrame(v)
                if columns is not None:
                    df.columns = columns
                df.index = df.index + 1
                df.index.name = '序号'
                df.to_excel(writer, sheet_name=k, index=isIndex)
                worksheet = writer.sheets[k]
                if widths is not None:
                    for i in range(len(widths)):
                        # 没有 Index 时,不用+1
                        worksheet.set_column(i + 1, i + 1, widths[i])
    except Exception as e:
        pass #2204# print(e)
    pass #2204# print("write over...")

if __name__ == '__main__':
    xlsx2List(r"C:\Users\ADMIN\Desktop\ee.xlsx")
